﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Zgq.CommonTools
{
    public class MSSqlDB : IC5SqlDB
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetInsertSql(object model, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            StringBuilder filedsSb = new StringBuilder();
            StringBuilder valuesSb = new StringBuilder();

            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

            foreach (PropertyInfo var in info)
            {
                if (var.DeclaringType == typeof(byte[])) continue;

                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = var.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                if (attr.Length == 0) continue;

                var c5SqlModel = attr[0] as C5SqlModelAttribute;

                if (c5SqlModel.AutoIncrement) continue;
                if (c5SqlModel.IsTimeStamp) continue;

                filedsSb.AppendFormat("[{0}], ", var.Name);

                object oVal = var.GetValue(model, null);
                if (oVal == null)
                    valuesSb.Append("null, ");
                else
                    valuesSb.AppendFormat("'{0}', ", oVal);
            }

            filedsSb.Remove(filedsSb.Length - 2, 2);
            valuesSb.Remove(valuesSb.Length - 2, 2);

            String sql = String.Format("INSERT INTO [{0}]({1}) values({2})", className, filedsSb, valuesSb);

            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetUpdateSql(object model, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            StringBuilder sqlStr = new StringBuilder();
            StringBuilder sqlWhereStr = new StringBuilder();

            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

            foreach (PropertyInfo var in info)
            {
                if (var.DeclaringType == typeof(byte[])) continue;
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = var.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                if (attr.Length == 0) continue;

                var c5SqlModel = attr[0] as C5SqlModelAttribute;

                if (c5SqlModel.IsTimeStamp) continue;

                object oVal = var.GetValue(model, null);
                if (c5SqlModel.PrimaryKey)
                {
                    if (sqlWhereStr.Length > 0) sqlWhereStr.Append(" AND ");
                    sqlWhereStr.AppendFormat("[{0}] = {1}", var.Name, oVal);

                    continue;
                }
                if (c5SqlModel.IsNotModif || c5SqlModel.AutoIncrement) continue;

                if (oVal == null)
                    sqlStr.AppendFormat("[{0}] = null, ", var.Name);
                else
                    sqlStr.AppendFormat("[{0}] = '{1}', ", var.Name, oVal);

            }

            sqlStr.Remove(sqlStr.Length - 2, 2);

            if (sqlWhereStr.Length == 0)
                throw new Exception("不存在主键，不能更新操作！");

            String sql = String.Format("UPDATE [{0}] SET {1} WHERE {2}", className, sqlStr, sqlWhereStr);
            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="lists"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetDeleteListSql(object[] lists, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            if (lists.Count() == 0) return null;
            StringBuilder sqlWhereStr = new StringBuilder();

            object _firstT = lists[0];

            Type type = _firstT.GetType();
            //获取类名
            String className = type.Name;
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

            StringBuilder strSingleTableWhere = new StringBuilder();
            foreach (var item in lists)
            {
                if (item.GetType() != type)
                    throw new Exception("只支持同一个对象批量删除");

                strSingleTableWhere.Clear();
                foreach (PropertyInfo var in info)
                {
                    //取得属性的特性标签，false表示不获取因为继承而得到的标签
                    Object[] attr = var.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                    if (attr.Length == 0) continue;
                    var c5SqlModel = attr[0] as C5SqlModelAttribute;

                    if (c5SqlModel.PrimaryKey)
                    {
                        object oVal = var.GetValue(item, null);

                        if (strSingleTableWhere.Length > 0) strSingleTableWhere.Append(" AND ");
                        strSingleTableWhere.AppendFormat("[{0}] = {1}", var.Name, oVal);
                    }
                }
                sqlWhereStr.AppendFormat("({0}) OR ", strSingleTableWhere);

                if (sqlWhereStr.Length < 8)
                    throw new Exception("不存在主键，不能执行删除操作！");
            }

            if (sqlWhereStr.Length < 8)
                throw new Exception("不存在主键，不能执行删除操作！");
            else
                sqlWhereStr.Remove(sqlWhereStr.Length - 4, 4);

            String sql = String.Format("DELETE [{0}] WHERE {1}", className, sqlWhereStr);

            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public string GetDeleteSql(object model, string sysDatabaseName = null, List<object> sqlparameters = null)
        {
            StringBuilder sqlWhereStr = new StringBuilder();

            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

            foreach (PropertyInfo var in info)
            {
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = var.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                if (attr.Length == 0) continue;
                var c5SqlModel = attr[0] as C5SqlModelAttribute;

                if (c5SqlModel.PrimaryKey)
                {
                    object oVal = var.GetValue(model, null);
                    if (sqlWhereStr.Length > 0) sqlWhereStr.Append(" AND ");
                    sqlWhereStr.AppendFormat("[{0}] = '{1}'", var.Name, oVal);
                }
            }

            if (sqlWhereStr.Length == 0)
                throw new Exception("不存在主键，不能执行删除操作！");

            String sql = String.Format("DELETE FROM [{0}] WHERE {1}", className, sqlWhereStr);

            return sql;
        }

        public string GetExistsSql(object model, string sysDatabaseName = null, string whereStr = null)
        {
            Type type = model.GetType();
            //获取类名
            String className = type.Name;
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendFormat("SELECT 1 FROM [{0}]", className);
            if (!string.IsNullOrEmpty(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            return sqlStr.ToString();
        }

        /// <summary>
        /// 查询数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string SelectList<T>(string sysDatabaseName = null, string whereStr = null, string orderByStr = null)
        {
            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            StringBuilder sqlStr = new StringBuilder();

            sqlStr.AppendFormat(" SELECT * FROM [{0}] ", className);

            if (!string.IsNullOrEmpty(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            if (!string.IsNullOrEmpty(orderByStr))
            {
                if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                    orderByStr = string.Concat(" ORDER BY ", orderByStr);
                sqlStr.AppendFormat(" {0} ", orderByStr);
            }

            return sqlStr.ToString();
        }

        #region 查询分页数据集
        /// <summary>
        /// 查询分页数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页数量</param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string SelectListPaging<T>(int pageIndex, int pageSize, string sysDatabaseName = null, string whereStr = null, string orderByStr = null)
        {
            int startIndex = pageIndex * pageSize + 1;

            int endIndex = (pageIndex + 1) * pageSize;

            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            //默认以主键排序
            if (string.IsNullOrEmpty(orderByStr))
            {
                PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

                //取出主键属性
                var parimaryKetPro = info.FirstOrDefault(o =>
                {
                    Object[] attr = o.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                    if (attr.Length == 0 || (attr[0] as C5SqlModelAttribute) == null || (attr[0] as C5SqlModelAttribute).PrimaryKey == true) return false;

                    return true;
                });

                if (parimaryKetPro == null) throw new Exception(string.Format("实体【{0}】无主键，请检查！", className));

                orderByStr = parimaryKetPro.Name;
            }

            if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                orderByStr = string.Concat(" ORDER BY ", orderByStr);

            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append(" SELECT * FROM ( ");

            sqlStr.AppendFormat("   SELECT ROW_NUMBER() OVER({0}) AS RowIndex,* FROM [{1}] ", orderByStr, className);

            if (!string.IsNullOrEmpty(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            sqlStr.Append(" ) AS TempTable ");

            sqlStr.AppendFormat(" WHERE RowIndex BETWEEN {0} AND {1} ", startIndex, endIndex);

            sqlStr.AppendFormat(" {0} ", orderByStr);

            return sqlStr.ToString();
        }

        /// <summary>
        /// 查询分页数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页数量</param>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public string SelectListPagingColumn<T>(int pageIndex, int pageSize, string sysDatabaseName = null, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            int startIndex = pageIndex * pageSize + 1;

            int endIndex = (pageIndex + 1) * pageSize;

            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            //默认以主键排序
            if (string.IsNullOrEmpty(orderByStr))
            {
                PropertyInfo[] info = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | System.Reflection.BindingFlags.DeclaredOnly);

                //取出主键属性
                var parimaryKetPro = info.FirstOrDefault(o =>
                {
                    Object[] attr = o.GetCustomAttributes(typeof(C5SqlModelAttribute), false);

                    if (attr.Length == 0 || (attr[0] as C5SqlModelAttribute) == null || (attr[0] as C5SqlModelAttribute).PrimaryKey == true) return false;

                    return true;
                });

                if (parimaryKetPro == null) throw new Exception(string.Format("实体【{0}】无主键，请检查！", className));

                orderByStr = parimaryKetPro.Name;
            }

            if (!orderByStr.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
                orderByStr = string.Concat(" ORDER BY ", orderByStr);

            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append(" SELECT * FROM ( ");

            sqlStr.AppendFormat("   SELECT ROW_NUMBER() OVER({0}) AS RowIndex,{1} FROM [{2}] ", orderByStr, string.Join(",", colums), className);

            if (!string.IsNullOrEmpty(whereStr))
            {
                if (!whereStr.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            sqlStr.Append(" ) AS TempTable ");

            sqlStr.AppendFormat(" WHERE RowIndex BETWEEN {0} AND {1} ", startIndex, endIndex);

            sqlStr.AppendFormat(" {0} ", orderByStr);

            return sqlStr.ToString();
        }
        #endregion

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public string SelectModel<T>(string sysDatabaseName = null, string whereStr = null, string orderByStr = null)
        {
            Type type = typeof(T);

            //获取类名
            String className = type.Name;

            StringBuilder sqlStr = new StringBuilder();

            sqlStr.AppendFormat("SELECT * FROM [{0}]", className);

            if (!string.IsNullOrEmpty(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }
            if (!string.IsNullOrEmpty(orderByStr))
            {
                sqlStr.AppendFormat(" ORDER BY  {0}", orderByStr);
            }

            return sqlStr.ToString();
        }

        #region 查询数据总量

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public string SelectModelCount<T>(string sysDatabaseName = null, string whereStr = null)
        {
            Type type = typeof(T);

            String className = type.Name;

            StringBuilder sqlStr = new StringBuilder();
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheView || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                var model = SqlCache.GetSqlByCode(sysDatabaseName, className);
                if (model == null)
                    throw new Exception("找不到表或者视图信息");

                if (model.CodeType == 1 || model.CodeType == 2)
                {
                    sqlStr.AppendFormat("SELECT count(*) FROM ({0}) as ResultView", className);
                }
                else
                {
                    sqlStr.AppendFormat("SELECT count(*) FROM [{0}]", className);
                }
            }
            else
            {
                sqlStr.AppendFormat("SELECT count(*) FROM [{0}]", className);
            }

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                if (!whereStr.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                    sqlStr.Append(" WHERE ");
                sqlStr.AppendFormat(" {0}", whereStr);
            }

            return sqlStr.ToString();
        }

        #endregion


        #region 查询Datatable

        public string SelectDatatable(string tableCode, string sysDatabaseName = null, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return "";
        }


        public string SelectDatatablePaging(string tableCode, int pageIndex, int pageSize, string sysDatabaseName = null, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return "";
        }

        public string SelectDatatableCount(string tableCode, string sysDatabaseName = null, string whereStr = null)
        {
            return "";
        }

        #endregion



        #region 视图部分

        /// <summary>
        /// 组合视图语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public string GetViewEntitySql<T>(string whereStr = null, string orderByStr = null)
        {
            throw new NotImplementedException();
        }

        public string GetViewListSql<T>(string whereStr = null, string orderByStr = null)
        {
            throw new NotImplementedException();
        }

        public string GetViewListPagingSql<T>(int pageIndex, int pageSize, string whereStr = null, string orderByStr = null)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region 获取字段信息

        /// <summary>
        /// 获取表字段信息
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <param name="prop"></param>
        /// <returns></returns>
        public C5SqlModelAttribute GetC5SqlModelAttribute(string sysDatabaseName, string tableName, PropertyInfo prop)
        {
            C5SqlModelAttribute c5SqlModel = null;
            if (C5DataBaseHelper.UseCache == CacheFlag.CacheColumn || C5DataBaseHelper.UseCache == CacheFlag.CacheAll)
            {
                if (string.IsNullOrWhiteSpace(sysDatabaseName))
                    throw new Exception("已启用缓存,调用方法需要sysDatabaseName参数");

                c5SqlModel = SqlCache.GetColumnInfo(sysDatabaseName, tableName, prop.Name);
            }
            else
            {
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = prop.GetCustomAttributes(typeof(C5SqlModelAttribute), false);
                if (attr.Length == 0)
                    return null;

                c5SqlModel = attr[0] as C5SqlModelAttribute;
            }

            return c5SqlModel;
        }

        #endregion
    }
}
